package zz.extjs.servlet;

import java.io.IOException;
import java.io.PrintWriter;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.json.JSONArray;
import org.json.JSONObject;

import zz.extjs.factory.DAOFactory;

public class ModifyResult extends HttpServlet {

	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {

		this.doPost(request, response);
	}

	private int InsertPerinfo(JSONObject jo) throws Exception {
		long nid = new java.util.Date().getTime(); // 生成随机id
		int result = -1;

		if (!(jo.isNull("sid")) && !("null".equals(jo.getString("sid")) || ""
				.equals(jo.getString("sid")))) {

			String sql = "insert into PERSON_INFO_TAB (PER_ID,   T_SID, E_NAME, C_NAME, K_NAME, PB_ID, T_BIRTHDAY, T_SEX,"
					+ " HAS_PHOTO, MEMO, PB_TYPE, PASS_ID, T_CLASS,"
					+ "T_STUDY,COS_ID, T_PBOUT, T_PBBEGIN) values('"
					+ nid
					+ "','"
					+ jo.getString("sid")
					+ "'"
					+ ","
					+ (!jo.isNull("ename")
							&& !("null".equals(jo.getString("ename")) || ""
									.equals(jo.getString("ename"))) ? "'"
							+ jo.getString("ename") + "'" : "null")
					+ ","
					+ (!jo.isNull("cname")
							&& !("null".equals(jo.getString("cname")) || ""
									.equals(jo.getString("cname"))) ? "'"
							+ jo.getString("cname") + "'" : "null")
					+ ","
					+ (!jo.isNull("kname")
							&& !("null".equals(jo.getString("kname")) || ""
									.equals(jo.getString("kname"))) ? "'"
							+ jo.getString("kname") + "'" : "null")
					+ ","
					+ (!jo.isNull("pb_id")
							&& !("null".equals(jo.getString("pb_id")) || ""
									.equals(jo.getString("pb_id"))) ? "'"
							+ jo.getString("pb_id") + "'" : "null")
					+ ","
					+ (!jo.isNull("birthday")
							&& !("null".equals(jo.getString("birthday")) || ""
									.equals(jo.getString("birthday"))) ? "'"
							+ jo.getString("birthday") + "'" : "null")
					+ ","
					+ (!jo.isNull("sex")
							&& !("null".equals(jo.getString("sex")) || ""
									.equals(jo.getString("sex"))) ? "'"
							+ jo.getString("sex") + "'" : "null")
					+ ","
					+ (!jo.isNull("hasphoto")
							&& !("null".equals(jo.getString("hasphoto")) || ""
									.equals(jo.getString("hasphoto"))) ? jo
							.getString("hasphoto") : "null") // bool
					+ ","
					+ (!jo.isNull("memo")
							&& !("null".equals(jo.getString("memo")) || ""
									.equals(jo.getString("memo"))) ? "'"
							+ jo.getString("memo") + "'" : "null")
					+ ","
					+ (!jo.isNull("pb_type")
							&& !("null".equals(jo.getString("pb_type")) || ""
									.equals(jo.getString("pb_type"))) ? "'"
							+ jo.getString("pb_type") + "'" : "null")
					+ ","
					+ (!jo.isNull("pass_id")
							&& !("null".equals(jo.getString("pass_id")) || ""
									.equals(jo.getString("pass_id"))) ? "'"
							+ jo.getString("pass_id") + "'" : "null")
					+ ","
					+ (!jo.isNull("iclass")
							&& !("null".equals(jo.getString("iclass")) || ""
									.equals(jo.getString("iclass"))) ? "'"
							+ jo.getString("iclass") + "'" : "null")
					+ ","
					+ (!jo.isNull("study")
							&& !("null".equals(jo.getString("study")) || ""
									.equals(jo.getString("study"))) ? "'"
							+ jo.getString("study") + "'" : "null")
					+ ","
					+ (!jo.isNull("course")
							&& !("null".equals(jo.getString("course")) || ""
									.equals(jo.getString("course"))) ? "(select COS_ID from COURSE_TAB where COS_NAME='"
							+ jo.getString("course") + "')"
							: "null")
					+ ","
					+ (!jo.isNull("pb_out")
							&& !("null".equals(jo.getString("pb_out")) || ""
									.equals(jo.getString("pb_out"))) ? "'"
							+ jo.getString("pb_out") + "'" : "null")
					+ ","
					+ (!jo.isNull("pb_begin")
							&& !("null".equals(jo.getString("pb_begin")) || ""
									.equals(jo.getString("pb_begin"))) ? "'"
							+ jo.getString("pb_begin") + "'" : "null") + ") ";

			result = DAOFactory.getStudentInstance().ExcuteSQL(sql, "insert");
		}
		return result;
	}

	private int UpdatePerinfo(JSONObject jo) throws Exception {		//update时，为null的数据就把相应sql表达式屏蔽
		int result = -1;
		if ((!(jo.isNull("id")) && !("null".equals(jo.getString("id")) || ""
				.equals(jo.getString("id"))))
				&& (!(jo.isNull("sid")) && !("null".equals(jo.getString("sid")) || ""
						.equals(jo.getString("sid"))))) {
			String sql = "update PERSON_INFO_TAB set ";
			sql += " T_SID='" + jo.getString("sid") + "'";
			/*************jo中属性存在，值为null或“”是，sql设null****************/
			 if(!jo.isNull("ename"))	
					sql += ",E_NAME=" + 
					(!("null".equals(jo.getString("ename")) || "".equals(jo.getString("ename"))) ? "'"+jo.getString("ename") + "'" : "null");
			 if(!jo.isNull("cname"))	
					sql += ",C_NAME=" + 
					(!("null".equals(jo.getString("cname")) || "".equals(jo.getString("cname"))) ? "'"+jo.getString("cname") + "'" : "null");
			 if(!jo.isNull("kname"))	
					sql += ",K_NAME=" + 
					(!("null".equals(jo.getString("kname")) || "".equals(jo.getString("kname"))) ? "'"+jo.getString("kname") + "'" : "null");
			 if(!jo.isNull("sex"))	
					sql += ",T_SEX=" + 
					(!("null".equals(jo.getString("sex")) || "".equals(jo.getString("sex"))) ? "'"+jo.getString("sex") + "'" : "null");
			 if(!jo.isNull("birthday"))	
					sql += ",T_BIRTHDAY=" + 
					(!("null".equals(jo.getString("birthday")) || "".equals(jo.getString("birthday"))) ? "'"+jo.getString("birthday") + "'" : "null");
				// sql += ",T_HATEL="
				// sql += ",HAS_STUCARD="
				// sql += ",PB_TIMEOUT="
				// sql += ",T_PHONE="
				// sql += ",CUR_CLASS="
				// sql += ",T_GRADE="
				// sql += ",T_ELECTIVE="			 
			 if(!jo.isNull("hasphoto"))	
					sql += ",HAS_PHOTO=" + 
					(!("null".equals(jo.getString("hasphoto")) || "".equals(jo.getString("hasphoto"))) ? jo.getString("hasphoto") : "null");
			 if(!jo.isNull("course"))	
					sql += ",COS_ID=" + 
					(!("null".equals(jo.getString("course")) || "".equals(jo.getString("course"))) 
							? "(select COS_ID from COURSE_TAB where COS_NAME='"+jo.getString("course") + "')" : "null");
			 if(!jo.isNull("study"))	
					sql += ",T_STUDY=" + 
					(!("null".equals(jo.getString("study")) || "".equals(jo.getString("study"))) ? "'"+jo.getString("study") + "'" : "null");
			 if(!jo.isNull("iclass"))	
					sql += ",T_CLASS=" + 
					(!("null".equals(jo.getString("iclass")) || "".equals(jo.getString("iclass"))) ? "'"+jo.getString("iclass") + "'" : "null");
			 if(!jo.isNull("pass_id"))	
					sql += ",PASS_ID=" + 
					(!("null".equals(jo.getString("pass_id")) || "".equals(jo.getString("pass_id"))) ? "'"+jo.getString("pass_id") + "'" : "null");
			 if(!jo.isNull("memo"))	
					sql += ",MEMO=" + 
					(!("null".equals(jo.getString("memo")) || "".equals(jo.getString("memo"))) ? "'"+jo.getString("memo") + "'" : "null");
			 if(!jo.isNull("pb_type"))	
					sql += ",PB_TYPE=" + 
					(!("null".equals(jo.getString("pb_type")) || "".equals(jo.getString("pb_type"))) ? "'"+jo.getString("pb_type") + "'" : "null");
			 if(!jo.isNull("pb_id"))	
					sql += ",PB_ID=" + 
					(!("null".equals(jo.getString("pb_id")) || "".equals(jo.getString("pb_id"))) ? "'"+jo.getString("pb_id") + "'" : "null");
			 if(!jo.isNull("pb_begin"))	
					sql += ",T_PBBEGIN=" + 
					(!("null".equals(jo.getString("pb_begin")) || "".equals(jo.getString("pb_begin"))) ? "'"+jo.getString("pb_begin") + "'" : "null");
			 if(!jo.isNull("pb_out"))	
					sql += ",T_PBOUT=" + 
					(!("null".equals(jo.getString("pb_out")) || "".equals(jo.getString("pb_out"))) ? "'"+jo.getString("pb_out") + "'" : "null");

			sql += " where PER_ID='" + jo.getString("id") + "' ";

			result = DAOFactory.getStudentInstance().ExcuteSQL(sql, "update");
		}
		return result;
	}

	private int InsertResult(String sid, String subname, String result)
			throws Exception {
		long nid = new java.util.Date().getTime(); // 生成随机id
		return DAOFactory
				.getStudentInstance()
				.ExcuteSQL(
						"insert into EXAM_LIST_TAB (EXAM_ID ,SCOPE_TAB,PER_ID,SUB_ID) values('"
								+ nid
								+ "','"
								+ result
								+ "',(select PER_ID from PERSON_INFO_TAB where T_SID='"
								+ sid
								+ "'),(select min(SUB_ID) from SUBJECT_TAB where SUB_NAME='"
								+ subname + "')) ", "insert");

	}

	private int InsertSubject(String subname, String course, String credits)
			throws Exception {
		long nid = new java.util.Date().getTime(); // 生成随机id
		return DAOFactory.getStudentInstance().ExcuteSQL(
				"insert into SUBJECT_TAB ( SUB_ID,SUB_NAME,COS_ID,SUB_CREDITS) values('"
						+ nid + "','" + subname
						+ "',(select COS_ID from COURSE_TAB where COS_NAME='"
						+ course + "'), "
						+ (credits != null ? "'" + credits + "'" : "null")
						+ ") ", "insert");

	}

	private int DeleteResult(String retid) throws Exception {
		return DAOFactory.getStudentInstance().ExcuteSQL(
				"DELETE from EXAM_LIST_TAB where EXAM_ID='" + retid + "' ",
				"delete");

	}

	private int DeleteSubject(String sqlwhere) throws Exception {
		return DAOFactory.getStudentInstance().ExcuteSQL(
				"DELETE from SUBJECT_TAB " + sqlwhere, "delete");

	}

	private int DeleteStudent(String perid) throws Exception {
		return DAOFactory.getStudentInstance().ExcuteSQL(
				"DELETE from PERSON_INFO_TAB where PER_ID='" + perid + "' ",
				"delete");

	}

	private int UpdateResult(String retid, String result, String subname)
			throws Exception {
		return DAOFactory
				.getStudentInstance()
				.ExcuteSQL(
						"update EXAM_LIST_TAB set SCOPE_TAB='"
								+ result
								+ "',SUB_ID=(select min(SUB_ID) from SUBJECT_TAB where SUB_NAME='"
								+ subname + "') where EXAM_ID='" + retid + "' ",
						"update");

	}

	private int UpdateSubdect(String subid, String subname, String course,
			String credits) throws Exception { // 如果update失败，假设为没有相应纪录，执行insert
		int r = DAOFactory
				.getStudentInstance()
				.ExcuteSQL(
						"update SUBJECT_TAB set SUB_NAME='"
								+ subname
								+ "', SUB_CREDITS="
								+ (credits != null ? "'" + credits + "'"
										: "null")
								+ ", COS_ID=(select COS_ID from COURSE_TAB where COS_NAME='"
								+ course + "') where SUB_ID='" + subid + "' ",
						"update");
		return r <= 0 ? InsertSubject(subname, course, credits) : r;
	}

	private int IsExist(String sqlwhere, String scope) throws Exception { // 检查是否存在，result和subject中是成绩或科目在exam、sub表中参数是where子句
		String sql = "";
		if ("result".equals(scope) || "subject".equals(scope)) // 省略
			sql = "select * from EXAM_LIST_TAB,SUBJECT_TAB " + sqlwhere + "' ";
		else if ("perinfo".equals(scope))
			sql = "select * from PERSON_INFO_TAB " + sqlwhere + "' ";

		return DAOFactory.getStudentInstance().ExcuteSQL(sql, "select");
	}

	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {

		response.setContentType("text/html; charset=utf-8");
		PrintWriter out = response.getWriter();

		// 登录检查
		Object zz = request.getSession().getAttribute("user1");

		if (zz == null) { // 先检查用户是否处于登入状态
			response.sendRedirect("logout.jsp");
			return;
		}

		String id = request.getParameter("id"); // id在delsubject是可能不止1个，“11，22，33”需要先分割
		String sid = request.getParameter("sid");
		String result = request.getParameter("result");
		String subname = request.getParameter("subname");
		String course = request.getParameter("course");
		String edit = request.getParameter("edit"); // 识别操作的表项
		String submitData = request.getParameter("submitData"); // 以json方式打包传送，修改subject时使用

		/**
		 * 暂时忽略参数检验
		 */

		// 遇到问题，exam表中scope是字符型,维持原状
		int r = -1;
		try {
			if ("delstu".equals(edit))
				r = DeleteStudent(id);
			else if ("perinfoedit".equals(edit)) { // personinfo页面，修改记录用
				JSONArray jarr = new JSONArray(submitData);
				for (int i = 0; i < jarr.length(); i++) {
					JSONObject jo = jarr.getJSONObject(i);
					if (!(jo.isNull("sid") || "".equals(jo.getString("sid")))) {
						if (!(jo.isNull("id") || "".equals(jo.getString("id"))))// id非空，为修改记录
							r = UpdatePerinfo(jo);
						else if (!(jo.isNull("sid") || "".equals(jo
								.getString("sid"))))// id空，检查sid非空则插入新纪录,要先检查是否有重复学号
							if (IsExist(" where T_SID='" + jo.getString("sid")
									+ "'", "perinfo") < 0)
								r = InsertPerinfo(jo);
					}
				}
			} else if ("result".equals(edit)) {
				JSONArray jarr = new JSONArray(submitData);
				for (int i = 0; i < jarr.length(); i++) {
					JSONObject jo = jarr.getJSONObject(i);
					if (!(jo.isNull("subname") || "".equals(jo
							.getString("subname")))
							&& !(jo.isNull("result") || "".equals(jo
									.getString("result")))) {
						if (!(jo.isNull("id") || "".equals(jo.getString("id"))))// id非空，为修改记录
							r = UpdateResult(jo.getString("id"), jo
									.getString("result"), jo
									.getString("subname"));
						else if (!(jo.isNull("sid") || "".equals(jo
								.getString("sid"))))// id空，检查sid,subname与result非空则插入新纪录
							r = InsertResult(jo.getString("sid"), jo
									.getString("subname"), jo
									.getString("result"));
					}
				}

				/*
				 * if(result!=null&&!("".equals(result.trim()))){
				 * if(id==null||("".equals(id.trim()))){ //添加新纪录 r =
				 * InsertResult(sid, subname, result); }else //修改记录 r =
				 * UpdateResult(id, result); }else{ //result为空说明要删除此条记录 r =
				 * DeleteResult(id); }
				 */
			} else if ("subname".equals(edit)) {
				// 改变subname操作，可能是改变名称已包含库中，或者尚未包含
				int rs = IsExist(" where SUB_NAME='" + subname + "' ",
						"subject");
				// 已包含的，查询sub表得到sub_id直接改变exam的sub_id
				// 未包含的，先插入到sub表，在修改exam
				// 学分暂时为空
				if (rs <= 0) {
					r = InsertSubject(subname, course, null);
					rs = 1;
				}
				if (rs > 0) {
					if (id == null || ("".equals(id.trim()))) { // 添加新纪录
						r = InsertResult(sid, subname, result);
					} else
						// 修改记录
						r = UpdateResult(id, result, subname);
				}
			} else if ("submng".equals(edit)) { // 修改课目记录,submitData参数jsonarray，可能是批量记录
				JSONArray jarr = new JSONArray(submitData);
				for (int i = 0; i < jarr.length(); i++) {
					JSONObject jo = jarr.getJSONObject(i);
					if (!(jo.isNull("sub_id") || "".equals(jo
							.getString("sub_id")))
							&& !(jo.isNull("subname") || "".equals(jo
									.getString("subname")))
							&& !(jo.isNull("course") || "".equals(jo
									.getString("course"))))// id非空，为修改记录
						r = UpdateSubdect(jo.getString("sub_id"), jo
								.getString("subname"), jo.getString("course"),
								jo.getString("credits"));
					else if (!(jo.isNull("subname") || "".equals(jo
							.getString("subname")))
							&& !(jo.isNull("course") || "".equals(jo
									.getString("course"))))// id空，检查subname与cos非空则插入新纪录
						r = InsertSubject(jo.getString("subname"), jo
								.getString("course"), jo.getString("credits"));
				}
			} else if ("delresult".equals(edit)) { // 删除成绩记录
				r = DeleteResult(id);
			} else if ("delsubject".equals(edit)) { // 删除课目记录
				// 传递的id参数可能不止一个id，需要先判断和分割
				String[] arr = id.split(",");
				String sqlwhere = "";
				for (int i = 0; i < arr.length; i++) {
					sqlwhere += " or SUB_ID='" + arr[i] + "' ";
				}
				sqlwhere = sqlwhere.replaceFirst("or", "where");
				// out.println(sqlwhere);
				r = DeleteSubject(sqlwhere);
			}
			// 反馈信息
			if (r < 0) {
				throw new NullPointerException("modifyresult error");
			} else
				out.print("{success:true}");
			out.flush();
			out.close();
			// form.reset(mapping, request);
		} catch (Exception e) {
			// out.print( "false" );
			throw new NullPointerException("modifyresult error");
		}
	}
}
